package com.example.othree.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.othree.entity.TowerOzone;
import com.example.othree.entity.TowerOzoneGradientObservations;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;


import java.time.LocalDateTime;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Mapper
public interface TowerOzoneMapper extends BaseMapper<TowerOzone> {
    //根据站点名和时间获取指定数据
    List<TowerOzone> findByPointNameAndTime(@Param("PointName") String pointName,
                                            @Param("time") String time);

    /**
     * 逐分钟查询
     */
    @Select("SELECT DATE_FORMAT(time, '%Y-%m-%d %H:%i') AS time, ${element} AS avgValue, pointName " +
            "FROM tower_ozone_gradient_observations_copy1 " +
            "WHERE time BETWEEN #{startTime} AND #{endTime} " +
            "and pointName LIKE CONCAT('%', #{stationName}, '%') " +
            "ORDER BY pointName, time")
    List<Map<String, Object>> getMinuteWindData(
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime,
            @Param("stationName") String stationName,
            @Param("element") String element);

//    @Select("SELECT time, ${element} AS value, pointName " +
//            "FROM tower_ozone_gradient_observations " +
//            "WHERE time BETWEEN #{startTime} AND #{endTime} " +
//            "and pointName LIKE CONCAT('%', #{stationName}, '%') " +
//            "ORDER BY pointName, time")
//    /**
//     * mysql5.1.32不支持LocalDateTime,用Date，已解决--mysql驱动版本改为5.1.37
//     */
//    List<Map<String, Object>> getMinuteWindData(
//            @Param("startTime") Date startTime,
//            @Param("endTime") Date endTime,
//            @Param("stationName") String stationName,
//            @Param("element") String element);

    /**
     * 按小时分组查询
     */
    @Select("SELECT DATE_FORMAT(time, '%Y-%m-%d %H:00:00') AS time, " +
            "AVG(${element}) AS avgValue, pointName " +
            "FROM tower_ozone_gradient_observations_copy1 " +
            "WHERE time BETWEEN #{startTime} AND #{endTime} " +
            "AND pointName LIKE CONCAT('%', #{stationName}, '%') " +
            "GROUP BY pointName, DATE_FORMAT(time, '%Y-%m-%d %H:00:00')")
    List<Map<String, Object>> getHourlyAverageWindData(
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime,
            @Param("stationName") String stationName,
            @Param("element") String element);

    /**
     * 按天分组查询
     */
    @Select("SELECT pointName, AVG(${element}) AS avgValue, DATE_FORMAT(time, '%Y-%m-%d') AS time " +
            "FROM tower_ozone_gradient_observations_copy1 " +
            "WHERE time BETWEEN #{startTime} AND #{endTime} " +
            "AND pointName LIKE CONCAT('%', #{stationName}, '%') " +
            "GROUP BY pointName, DATE_FORMAT(time, '%Y-%m-%d')")
    List<Map<String, Object>> getDailyAverageWindData(
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime,
            @Param("stationName") String stationName,
            @Param("element") String element);

    @Select("SELECT DATE_FORMAT(time, '%Y-%m-%d %H:%i') AS time, pointName, Ma_wind as wind1, Ma_wdir as wdir1, US_wind as wind2, US_wdir as wdir2, US_W as ww2, rh, temp " +
            "FROM tower_ozone_gradient_observations_copy1 " +
            "WHERE time BETWEEN #{startTime} AND #{endTime} " +
            "AND pointName LIKE CONCAT('%', #{stationName}, '%') " +
            "ORDER BY pointName, time")
    List<TowerOzone> getAllData(
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime,
            @Param("stationName") String stationName);

    @Select("SELECT " +
            "AVG(${element}) AS avgValue, pointName " +
            "FROM tower_ozone_gradient_observations_copy1 " +
            "WHERE time BETWEEN #{startTime} AND #{endTime} " +
            "AND pointName LIKE CONCAT('%', #{stationName}, '%') " +
            "GROUP BY pointName")
    List<Map<String, Object>> getHourly8AverageWindData(
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime,
            @Param("stationName") String stationName,
            @Param("element") String element);

    List<Map<String, Object>> queryMinuteData(@Param("startTime") LocalDateTime startTime,
                                              @Param("endTime") LocalDateTime endTime,
                                              @Param("stationName") String stationName);

    List<Map<String, Object>> queryHourlyData(@Param("startTime") LocalDateTime startTime,
                                              @Param("endTime") LocalDateTime endTime,
                                              @Param("stationName") String stationName);

    List<Map<String, Object>> queryDailyData(@Param("startTime") LocalDateTime startTime,
                                              @Param("endTime") LocalDateTime endTime,
                                              @Param("stationName") String stationName);

    List<Map<String, Object>> query8HourlyData(@Param("startTime") LocalDateTime startTime,
                                             @Param("endTime") LocalDateTime endTime,
                                             @Param("stationName") String stationName);



}
